/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.ArrayList;
import java.util.List;
import models.Lesson;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

/**
 *
 * @author Michel
 */
public class LessonDao {

    private final Session session;

    public LessonDao(Session session) {
        this.session = session;
    }

    public List<Lesson> findAll() {
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery("from Lesson as l ORDER BY l.name ASC").setMaxResults(15).list();
        tx.commit();
        return list;
    }

    //find all lesson objects in a course (by course id)
    public List<Lesson> findAllByCourse(long id) {
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery("from Lesson as l where course_id= :id ORDER BY l.name ASC").setParameter("id", id).setMaxResults(15).list();
        tx.commit();
        return list;
    }

    //find all free lesson objects in a course (by course id)
    public List<Lesson> findAllFreeInCourse(long id) {
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery("from Lesson as l where course_id= :id AND freelesson=true ORDER BY l.name ASC").setParameter("id", id).setMaxResults(15).list();
        tx.commit();
        return list;
    }

    //find all lessons given by a teacher (id)
    public List<Lesson> findAllByTeacher(long id) {
        String hql = "from Lesson as l"
                + " join fetch l.course as c"
                + " join fetch c.teacher as t"
                + " where c.id = l.course.id"
                + " and t.id = :id"
                + "ORDER BY l.name ASC";
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery(hql).setParameter("id", id).list();
        tx.commit();
        return list;
    }

    //find all lesson objects by a teacher id
    public List<Lesson> findLessonsByTeacher(long id) {
        String hql = "from Lesson where course_id IN (select c.course_id from Course_teacher c where c.teacher_id= :id)";
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery(hql).setParameter("id", id).list();
        tx.commit();
        return list;
    }

//find all lesson objects by a teacher id
    public List<Lesson> findLesAllByTeacher(long id) {
        Transaction tx = session.beginTransaction();
        String sql = "select lesson_id from Lesson where course_id IN (select c.course_id from Course_teacher c where c.teacher_id= :id)";
        Query query = session.createSQLQuery(sql).setParameter("id", id);
        List idlist = query.list();
        ArrayList<Lesson> list = new ArrayList<Lesson>();
        for (int i = 0; i < idlist.size(); i++) {
            query = session.createQuery("from Lesson where lesson_id= :id").setParameter("id", Long.parseLong(idlist.get(i).toString()));
            Lesson course = get(Long.parseLong(idlist.get(i).toString()));
            list.add(course);
        }
        tx.commit();
        return list;
    }

    //get the pagination of a lesson (lesson list)
    public List<Lesson> getPagination(long course_id, int page) {
        int offset = (page - 1) * 15;
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery("from Lesson as l where course_id= :course_id ORDER BY l.name ASC").setParameter("course_id", course_id).setFirstResult(offset).setMaxResults(15).list();
        tx.commit();
        return list;
    }

    //get the last page 
    public int getLastPage(long course_id) {
        Transaction tx = session.beginTransaction();
        Query query = session.createSQLQuery("SELECT COUNT(*) FROM Lesson WHERE course_id = " + course_id);
        tx.commit();
        String i = "" + query.uniqueResult();
        return Integer.parseInt(i);
    }

    public boolean save(Lesson Lesson) {
        try {
            Transaction tx = session.beginTransaction();
            session.save(Lesson);
            tx.commit();
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public boolean update(Lesson Lesson) {
        try {
            Transaction tx = session.beginTransaction();
            session.update(Lesson);
            tx.commit();
            return true;
        } catch (Exception e) {
            // System.out.println("Error during update: " + e);
            return false;
        }
    }

    //get the last lesson object which is inserted
    public Lesson getLast() {
        Transaction tx = session.beginTransaction();
        List<Lesson> list = session.createQuery("from Lesson ORDER BY lesson_id DESC").setMaxResults(1).list();
        tx.commit();
        Lesson lastLesson = list.get(0);
        return lastLesson;
    }

    public Lesson get(long id) {
        return (Lesson) session.get(Lesson.class, id);
    }

    public void delete(Lesson Lesson) {
        Transaction tx = session.beginTransaction();
        session.delete(Lesson);
        tx.commit();
    }
}
